package com.TeaManage.servlet;

import com.TeaManage.bean.*;
import com.TeaManage.bean.*;
import com.TeaManage.util.GokJDBCUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@WebServlet("/user/*")
public class UserServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        request.setCharacterEncoding("utf-8");
        String pathInfo = request.getPathInfo();
        System.out.println("pathInfo: " + pathInfo);
        if (pathInfo == null) {
            response.sendRedirect(request.getContextPath() + "/views/user/index.jsp");
            return;
        }

        switch (pathInfo) {
            case "/shop":
                handleShop(request, response);
                break;
            case "/cart":
                handleCart(request, response);
                break;
            case "/orders":
                handleOrders(request, response);
                break;
            case "/account":
                handleAccount(request, response);
                break;
            case "/product":
                handleProductDetail(request, response);
                break;
            case "/categories":
                handleCategories(request, response);
                break;
            case "/user/orderDetail":
                handleOrderDetail(request, response);
                break;
            default:
                response.sendRedirect(request.getContextPath() + "/views/user/index.jsp");
                break;
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String pathInfo = request.getPathInfo();

        if (pathInfo == null) {
            response.sendRedirect(request.getContextPath() + "/views/user/index.jsp");
            return;
        }

        switch (pathInfo) {
            case "/addToCart":
                handleAddToCart(request, response);
                break;
            case "/updateCart":
                handleUpdateCart(request, response);
                break;
            case "/removeFromCart":
                handleRemoveFromCart(request, response);
                break;
            case "/orders":
                handleCheckout(request, response);
                break;
            case "/updateAccount":
                handleUpdateAccount(request, response);
                break;
            default:
                response.sendRedirect(request.getContextPath() + "/views/user/index.jsp");
                break;
        }
    }

    // 处理商城页面
    private void handleShop(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            String categoryId = request.getParameter("category");
            String searchKeyword = request.getParameter("search");
            String pageStr = request.getParameter("page");

            int page = 1;
            int pageSize = 12;

            if (pageStr != null && !pageStr.isEmpty()) {
                try {
                    page = Integer.parseInt(pageStr);
                } catch (NumberFormatException e) {
                    page = 1;
                }
            }

            Connection conn = GokJDBCUtil.getConnection();
            if (conn == null) {
                System.out.println("数据库连接失败！");
                response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "数据库连接失败");
                return;
            }
            System.out.println("数据库连接成功！");

            // 获取茶叶分类
            List<TeaCategory> categories = getTeaCategories(conn);
            System.out.println("获取到的分类数量: " + categories.size());
            request.setAttribute("categories", categories);

            // 构建查询条件
            StringBuilder sql = new StringBuilder("SELECT t.*, tc.category_name FROM tea t " +
                    "LEFT JOIN tea_category tc ON t.tea_category_id = tc.category_id WHERE 1=1");
            List<Object> params = new ArrayList<>();
            System.out.println("基础SQL: " + sql.toString());

            if (categoryId != null && !categoryId.isEmpty() && !categoryId.equals("0")) {
                sql.append(" AND t.tea_category_id = ?");
                params.add(Integer.parseInt(categoryId));
            }

            if (searchKeyword != null && !searchKeyword.trim().isEmpty()) {
                sql.append(" AND (t.tea_name LIKE ? OR t.description LIKE ?)");
                String keyword = "%" + searchKeyword.trim() + "%";
                params.add(keyword);
                params.add(keyword);
            }

            // 获取总记录数
            String countSql = "SELECT COUNT(*) FROM (" + sql.toString() + ") AS temp";
            PreparedStatement countStmt = conn.prepareStatement(countSql);
            for (int i = 0; i < params.size(); i++) {
                countStmt.setObject(i + 1, params.get(i));
            }
            ResultSet countRs = countStmt.executeQuery();
            int totalRecords = 0;
            if (countRs.next()) {
                totalRecords = countRs.getInt(1);
            }
            countRs.close();
            countStmt.close();

            // 分页查询
            sql.append(" ORDER BY t.tea_id DESC LIMIT ?, ?");
            params.add((page - 1) * pageSize);
            params.add(pageSize);

            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            System.out.println("执行查询SQL: " + sql.toString());
            System.out.println("查询参数: " + params.toString());

            ResultSet rs = stmt.executeQuery();
            List<Tea> teaList = new ArrayList<>();

            while (rs.next()) {
                Tea tea = new Tea();
                tea.setTea_id(rs.getInt("tea_id"));
                tea.setTea_category_id(rs.getInt("tea_category_id"));
                tea.setTea_name(rs.getString("tea_name"));
                tea.setDescription(rs.getString("description"));
                tea.setPrice(rs.getDouble("price"));
                tea.setImage(rs.getString("image"));
                tea.setUnit(rs.getString("unit"));
                teaList.add(tea);
                System.out.println("找到茶叶: " + tea.getTea_name());
            }

            System.out.println("总共找到茶叶数量: " + teaList.size());

            rs.close();
            stmt.close();
            conn.close();

            // 计算分页信息
            int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

            request.setAttribute("teaList", teaList);
            request.setAttribute("currentPage", page);
            request.setAttribute("totalPages", totalPages);
            request.setAttribute("totalRecords", totalRecords);
            request.setAttribute("selectedCategory", categoryId);
            request.setAttribute("searchKeyword", searchKeyword);

            request.getRequestDispatcher("/views/user/shop.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "数据库错误");
        }
    }

    // 获取茶叶分类
    private List<TeaCategory> getTeaCategories(Connection conn) throws SQLException {
        List<TeaCategory> categories = new ArrayList<>();
        String sql = "SELECT * FROM tea_category ORDER BY category_id";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            TeaCategory category = new TeaCategory();
            category.setCategory_id(rs.getInt("category_id"));
            category.setCategory_name(rs.getString("category_name"));
            category.setDescription(rs.getString("description"));
            categories.add(category);
        }

        rs.close();
        stmt.close();
        return categories;
    }

    // 处理购物车页面
    private void handleCart(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");

        if (customerId == null) {
            response.sendRedirect(request.getContextPath() + "/views/login.jsp");
            return;
        }

        try {
            Connection conn = GokJDBCUtil.getConnection();

            // 获取用户购物车
            String sql = "SELECT ci.*, t.tea_name, t.image, t.unit " +
                    "FROM cart_item ci " +
                    "JOIN cart c ON ci.cart_id = c.id " +
                    "JOIN tea t ON ci.tea_id = t.tea_id " +
                    "WHERE c.customer_id = ? " +
                    "ORDER BY ci.added_at DESC";

            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, customerId);
            ResultSet rs = stmt.executeQuery();

            List<CartItem> cartItems = new ArrayList<>();
            double totalAmount = 0.0;

            while (rs.next()) {
                CartItem item = new CartItem();
                item.setId(rs.getInt("id"));
                item.setTea_id(rs.getInt("tea_id"));
                item.setCart_id(rs.getInt("cart_id"));
                item.setQuantity(rs.getString("quantity"));
                item.setPrice(rs.getString("price"));
                item.setSelected(rs.getInt("selected"));
                item.setAdded_at(rs.getDate("added_at"));

                // 设置茶叶信息（用于显示）
                Tea tea = new Tea();
                tea.setTea_name(rs.getString("tea_name"));
                tea.setImage(rs.getString("image"));
                tea.setUnit(rs.getString("unit"));
                item.setTea(tea); // 将茶叶信息设置到购物车项中

                cartItems.add(item);

                // 计算总金额
                if (item.getSelected() == 1) {
                    double quantity = Double.parseDouble(item.getQuantity());
                    double price = Double.parseDouble(item.getPrice());
                    totalAmount += quantity * price;
                }
            }

            rs.close();
            stmt.close();
            conn.close();

            request.setAttribute("cartItems", cartItems);
            request.setAttribute("totalAmount", totalAmount);

            request.getRequestDispatcher("/views/user/cart.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "数据库错误");
        }
    }

    // 处理订单页面
    private void handleOrders(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");

        if (customerId == null) {
            response.sendRedirect(request.getContextPath() + "/views/user/login.jsp");
            return;
        }

        try {
            Connection conn = GokJDBCUtil.getConnection();

            String sql = "SELECT o.*, c.customer_name FROM `orders` o " +
                    "JOIN customer c ON o.customer_id = c.customer_id " +
                    "WHERE o.customer_id = ? " +
                    "ORDER BY o.order_id DESC";

            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, customerId);
            ResultSet rs = stmt.executeQuery();

            List<Order> orders = new ArrayList<>();

            while (rs.next()) {
                Order order = new Order();
                order.setOrder_id(rs.getInt("order_id"));
                order.setCustomer_id(rs.getInt("customer_id"));
                order.setOrder_no(rs.getString("order_no"));
                order.setTotal_amount(rs.getDouble("total_amount"));
                order.setStatus(rs.getInt("status"));
                order.setPayment_time(rs.getTimestamp("payment_time"));
                order.setCustomer_name(rs.getString("customer_name"));
                orders.add(order);
            }

            rs.close();
            stmt.close();
            conn.close();

            request.setAttribute("orders", orders);
            request.getRequestDispatcher("/views/user/orders.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "数据库错误");
        }
    }

    // 处理个人账户页面
    private void handleAccount(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");

        if (customerId == null) {
            response.sendRedirect(request.getContextPath() + "/views/user/login.jsp");
            return;
        }

        try {
            Connection conn = GokJDBCUtil.getConnection();

            String sql = "SELECT * FROM customer WHERE customer_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, customerId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Customer customer = new Customer();
                customer.setCustomer_id(rs.getInt("customer_id"));
                customer.setCustomer_name(rs.getString("customer_name"));
                customer.setPhone(rs.getString("phone"));
                customer.setEmail(rs.getString("email"));
                customer.setGender(rs.getInt("gender"));
                customer.setStatus(rs.getInt("status"));

                request.setAttribute("customer", customer);
            }

            rs.close();
            stmt.close();
            conn.close();

            request.getRequestDispatcher("/views/user/my-account.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "数据库错误");
        }
    }

    // 处理产品详情页面
    private void handleProductDetail(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String teaIdStr = request.getParameter("id");

        if (teaIdStr == null || teaIdStr.isEmpty()) {
            response.sendRedirect(request.getContextPath() + "/user/shop");
            return;
        }

        try {
            int teaId = Integer.parseInt(teaIdStr);
            Connection conn = GokJDBCUtil.getConnection();

            String sql = "SELECT t.*, tc.category_name FROM tea t " +
                    "LEFT JOIN tea_category tc ON t.tea_category_id = tc.category_id " +
                    "WHERE t.tea_id = ?";

            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, teaId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Tea tea = new Tea();
                tea.setTea_id(rs.getInt("tea_id"));
                tea.setTea_category_id(rs.getInt("tea_category_id"));
                tea.setTea_name(rs.getString("tea_name"));
                tea.setDescription(rs.getString("description"));
                tea.setPrice(rs.getDouble("price"));
                tea.setImage(rs.getString("image"));
                tea.setUnit(rs.getString("unit"));

                TeaCategory category = new TeaCategory();
                category.setCategory_name(rs.getString("category_name"));

                request.setAttribute("tea", tea);
                request.setAttribute("category", category);
            } else {
                response.sendRedirect(request.getContextPath() + "/user/shop");
                return;
            }

            rs.close();
            stmt.close();
            conn.close();

            request.getRequestDispatcher("/views/user/single-product.jsp").forward(request, response);

        } catch (NumberFormatException | SQLException e) {
            e.printStackTrace();
            response.sendRedirect(request.getContextPath() + "/user/shop");
        }
    }

    // 处理分类请求
    private void handleCategories(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            Connection conn = GokJDBCUtil.getConnection();
            List<TeaCategory> categories = getTeaCategories(conn);
            conn.close();

            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");

            StringBuilder json = new StringBuilder("[");
            for (int i = 0; i < categories.size(); i++) {
                TeaCategory category = categories.get(i);
                json.append("{\"id\":").append(category.getCategory_id())
                        .append(",\"name\":\"").append(category.getCategory_name()).append("\"}");
                if (i < categories.size() - 1) {
                    json.append(",");
                }
            }
            json.append("]");

            response.getWriter().write(json.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "数据库错误");
        }
    }

    // 添加到购物车
    private void handleAddToCart(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");

        if (customerId == null) {
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"请先登录\"}");
            return;
        }

        try {
            String teaIdStr = request.getParameter("teaId");
            String quantityStr = request.getParameter("quantity");

            if (teaIdStr == null || quantityStr == null) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"参数错误\"}");
                return;
            }

            int teaId = Integer.parseInt(teaIdStr);
            double quantity = Double.parseDouble(quantityStr);

            Connection conn = GokJDBCUtil.getConnection();

            // 获取茶叶价格
            String teaSql = "SELECT price FROM tea WHERE tea_id = ?";
            PreparedStatement teaStmt = conn.prepareStatement(teaSql);
            teaStmt.setInt(1, teaId);
            ResultSet teaRs = teaStmt.executeQuery();

            if (!teaRs.next()) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"商品不存在\"}");
                teaRs.close();
                teaStmt.close();
                conn.close();
                return;
            }

            double price = teaRs.getDouble("price");
            teaRs.close();
            teaStmt.close();

            // 获取或创建购物车
            String cartSql = "SELECT id FROM cart WHERE customer_id = ?";
            PreparedStatement cartStmt = conn.prepareStatement(cartSql);
            cartStmt.setInt(1, customerId);
            ResultSet cartRs = cartStmt.executeQuery();

            int cartId;
            if (cartRs.next()) {
                cartId = cartRs.getInt("id");
            } else {
                // 创建新购物车
                String createCartSql = "INSERT INTO cart (customer_id) VALUES (?)";
                PreparedStatement createCartStmt = conn.prepareStatement(createCartSql, PreparedStatement.RETURN_GENERATED_KEYS);
                createCartStmt.setInt(1, customerId);
                createCartStmt.executeUpdate();

                ResultSet generatedKeys = createCartStmt.getGeneratedKeys();
                if (generatedKeys.next()) {
                    cartId = generatedKeys.getInt(1);
                } else {
                    response.setContentType("application/json");
                    response.getWriter().write("{\"success\":false,\"message\":\"创建购物车失败\"}");
                    generatedKeys.close();
                    createCartStmt.close();
                    cartRs.close();
                    cartStmt.close();
                    conn.close();
                    return;
                }
                generatedKeys.close();
                createCartStmt.close();
            }
            cartRs.close();
            cartStmt.close();

            // 检查商品是否已在购物车中
            String checkSql = "SELECT id, quantity FROM cart_item WHERE cart_id = ? AND tea_id = ?";
            PreparedStatement checkStmt = conn.prepareStatement(checkSql);
            checkStmt.setInt(1, cartId);
            checkStmt.setInt(2, teaId);
            ResultSet checkRs = checkStmt.executeQuery();

            if (checkRs.next()) {
                // 更新数量
                int itemId = checkRs.getInt("id");
                double existingQuantity = Double.parseDouble(checkRs.getString("quantity"));
                double newQuantity = existingQuantity + quantity;

                String updateSql = "UPDATE cart_item SET quantity = ?, price = ? WHERE id = ?";
                PreparedStatement updateStmt = conn.prepareStatement(updateSql);
                updateStmt.setString(1, String.valueOf(newQuantity));
                updateStmt.setString(2, String.valueOf(price));
                updateStmt.setInt(3, itemId);
                updateStmt.executeUpdate();
                updateStmt.close();
            } else {
                // 添加新商品
                String insertSql = "INSERT INTO cart_item (tea_id, cart_id, quantity, price, selected, added_at) VALUES (?, ?, ?, ?, 1, NOW())";
                PreparedStatement insertStmt = conn.prepareStatement(insertSql);
                insertStmt.setInt(1, teaId);
                insertStmt.setInt(2, cartId);
                insertStmt.setString(3, String.valueOf(quantity));
                insertStmt.setString(4, String.valueOf(price));
                insertStmt.executeUpdate();
                insertStmt.close();
            }

            checkRs.close();
            checkStmt.close();
            conn.close();

            response.setContentType("application/json");
            response.getWriter().write("{\"success\":true,\"message\":\"添加成功\"}");

        } catch (NumberFormatException | SQLException e) {
            e.printStackTrace();
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"添加失败\"}");
        }
    }

    // 更新购物车
    private void handleUpdateCart(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");

        if (customerId == null) {
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"请先登录\"}");
            return;
        }

        try {
            String itemIdStr = request.getParameter("itemId");
            String quantityStr = request.getParameter("quantity");
            String selectedStr = request.getParameter("selected");

            if (itemIdStr == null) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"参数错误\"}");
                return;
            }

            int itemId = Integer.parseInt(itemIdStr);
            Connection conn = GokJDBCUtil.getConnection();

            StringBuilder updateSql = new StringBuilder("UPDATE cart_item SET ");
            List<Object> params = new ArrayList<>();
            boolean hasUpdate = false;

            if (quantityStr != null && !quantityStr.isEmpty()) {
                updateSql.append("quantity = ?");
                params.add(quantityStr);
                hasUpdate = true;
            }

            if (selectedStr != null && !selectedStr.isEmpty()) {
                if (hasUpdate) updateSql.append(", ");
                updateSql.append("selected = ?");
                params.add(Integer.parseInt(selectedStr));
                hasUpdate = true;
            }

            if (!hasUpdate) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"没有更新内容\"}");
                conn.close();
                return;
            }

            updateSql.append(" WHERE id = ? AND cart_id IN (SELECT id FROM cart WHERE customer_id = ?)");
            params.add(itemId);
            params.add(customerId);

            PreparedStatement stmt = conn.prepareStatement(updateSql.toString());
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            int affected = stmt.executeUpdate();
            stmt.close();
            conn.close();

            if (affected > 0) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":true,\"message\":\"更新成功\"}");
            } else {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"更新失败\"}");
            }

        } catch (NumberFormatException | SQLException e) {
            e.printStackTrace();
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"更新失败\"}");
        }
    }

    // 从购物车移除商品
    private void handleRemoveFromCart(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");

        if (customerId == null) {
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"请先登录\"}");
            return;
        }

        try {
            String itemIdStr = request.getParameter("itemId");

            if (itemIdStr == null) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"参数错误\"}");
                return;
            }

            int itemId = Integer.parseInt(itemIdStr);
            Connection conn = GokJDBCUtil.getConnection();

            String sql = "DELETE FROM cart_item WHERE id = ? AND cart_id IN (SELECT id FROM cart WHERE customer_id = ?)";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, itemId);
            stmt.setInt(2, customerId);

            int affected = stmt.executeUpdate();
            stmt.close();
            conn.close();

            if (affected > 0) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":true,\"message\":\"删除成功\"}");
            } else {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"删除失败\"}");
            }

        } catch (NumberFormatException | SQLException e) {
            e.printStackTrace();
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"删除失败\"}");
        }
    }

    // 处理结账
    private void handleCheckout(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");

        if (customerId == null) {
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"请先登录\"}");
            return;
        }

        try {
            Connection conn = GokJDBCUtil.getConnection();
            conn.setAutoCommit(false);

            // 获取选中的购物车商品
            String cartSql = "SELECT ci.*, t.tea_name FROM cart_item ci " +
                    "JOIN cart c ON ci.cart_id = c.id " +
                    "JOIN tea t ON ci.tea_id = t.tea_id " +
                    "WHERE c.customer_id = ? AND ci.selected = 1";

            PreparedStatement cartStmt = conn.prepareStatement(cartSql);
            cartStmt.setInt(1, customerId);
            ResultSet cartRs = cartStmt.executeQuery();

            List<CartItem> selectedItems = new ArrayList<>();
            double totalAmount = 0.0;

            while (cartRs.next()) {
                CartItem item = new CartItem();
                item.setId(cartRs.getInt("id"));
                item.setTea_id(cartRs.getInt("tea_id"));
                item.setQuantity(cartRs.getString("quantity"));
                item.setPrice(cartRs.getString("price"));
                selectedItems.add(item);

                double quantity = Double.parseDouble(item.getQuantity());
                double price = Double.parseDouble(item.getPrice());
                totalAmount += quantity * price;
            }
            cartRs.close();
            cartStmt.close();

            if (selectedItems.isEmpty()) {
                conn.rollback();
                conn.close();
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"请选择要结算的商品\"}");
                return;
            }

            // 生成订单号
            String orderNo = "ORD" + System.currentTimeMillis();

            // 创建订单
            String orderSql = "INSERT INTO `orders` (customer_id, order_no, total_amount, status, payment_time) VALUES (?, ?, ?, 1, NOW())";
            PreparedStatement orderStmt = conn.prepareStatement(orderSql, PreparedStatement.RETURN_GENERATED_KEYS);
            orderStmt.setInt(1, customerId);
            orderStmt.setString(2, orderNo);
            orderStmt.setDouble(3, totalAmount);
            orderStmt.executeUpdate();

            ResultSet orderKeys = orderStmt.getGeneratedKeys();
            int orderId = 0;
            if (orderKeys.next()) {
                orderId = orderKeys.getInt(1);
            }
            orderKeys.close();
            orderStmt.close();

            if (orderId == 0) {
                conn.rollback();
                conn.close();
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"创建订单失败\"}");
                return;
            }

            // 创建订单项
            String orderItemSql = "INSERT INTO order_item (order_id, tea_id, quantity, unit_price) VALUES (?, ?, ?, ?)";
            PreparedStatement orderItemStmt = conn.prepareStatement(orderItemSql);

            for (CartItem item : selectedItems) {
                double quantity = Double.parseDouble(item.getQuantity());
                double price = Double.parseDouble(item.getPrice());
                double subtotal = quantity * price;

                orderItemStmt.setInt(1, orderId);
                orderItemStmt.setInt(2, item.getTea_id());
                orderItemStmt.setDouble(3, quantity);
                orderItemStmt.setDouble(4, price);
//                orderItemStmt.setDouble(5, subtotal);
                orderItemStmt.addBatch();
            }
            orderItemStmt.executeBatch();
            orderItemStmt.close();

            // 删除已结算的购物车商品
            String deleteSql = "DELETE FROM cart_item WHERE cart_id IN (SELECT id FROM cart WHERE customer_id = ?) AND selected = 1";
            PreparedStatement deleteStmt = conn.prepareStatement(deleteSql);
            deleteStmt.setInt(1, customerId);
            deleteStmt.executeUpdate();
            deleteStmt.close();

            conn.commit();
            conn.close();

            response.setContentType("application/json");
            response.getWriter().write("{\"success\":true,\"message\":\"订单创建成功\",\"orderNo\":\"" + orderNo + "\"}");

        } catch (SQLException e) {
            e.printStackTrace();
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"结算失败\"}");
        }
    }

    // 更新账户信息
    private void handleUpdateAccount(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");

        if (customerId == null) {
            response.sendRedirect(request.getContextPath() + "/views/user/login-register.jsp");
            return;
        }

        try {
            String customerName = request.getParameter("customerName");
            String phone = request.getParameter("phone");
            String email = request.getParameter("email");
            String genderStr = request.getParameter("gender");

            Connection conn = GokJDBCUtil.getConnection();

            String sql = "UPDATE customer SET customer_name = ?, phone = ?, email = ?, gender = ? WHERE customer_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, customerName);
            stmt.setString(2, phone);
            stmt.setString(3, email);
            stmt.setInt(4, Integer.parseInt(genderStr));
            stmt.setInt(5, customerId);

            int affected = stmt.executeUpdate();
            stmt.close();
            conn.close();

            if (affected > 0) {
                // 更新session中的用户名
                session.setAttribute("customerName", customerName);
                request.setAttribute("message", "更新成功");
            } else {
                request.setAttribute("error", "更新失败");
            }

            // 重定向回账户页面
            response.sendRedirect(request.getContextPath() + "/user/account");

        } catch (NumberFormatException | SQLException e) {
            e.printStackTrace();
            request.setAttribute("error", "更新失败");
            response.sendRedirect(request.getContextPath() + "/user/account");
        }
    }

    // 处理订单详情
    private void handleOrderDetail(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        Integer customerId = (Integer) session.getAttribute("customerId");
        if (customerId == null) {
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"请先登录\"}");
            System.out.println("请先登录");
            return;
        }
        System.out.println("进入订单详情");
        try {
            String orderIdStr = request.getParameter("orderId");

            if (orderIdStr == null) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"参数错误\"}");
                return;
            }

            int orderId = Integer.parseInt(orderIdStr);
            Connection conn = GokJDBCUtil.getConnection();

            // 获取订单信息
            String orderSql = "SELECT o.*, c.customer_name FROM orders o\n" +
                    "JOIN customer c ON o.customer_id = c.customer_id\n" +
                    "WHERE o.order_id = ? AND o.customer_id = ?";

            PreparedStatement orderStmt = conn.prepareStatement(orderSql);
            orderStmt.setInt(1, orderId);
            orderStmt.setInt(2, customerId);
            ResultSet orderRs = orderStmt.executeQuery();

            if (!orderRs.next()) {
                response.setContentType("application/json");
                response.getWriter().write("{\"success\":false,\"message\":\"订单不存在\"}");
                orderRs.close();
                orderStmt.close();
                conn.close();
                return;
            }

            Order order = new Order();
            order.setOrder_id(orderRs.getInt("order_id"));
            order.setCustomer_id(orderRs.getInt("customer_id"));
            order.setOrder_no(orderRs.getString("order_no"));
            order.setTotal_amount(orderRs.getDouble("total_amount"));
            order.setStatus(orderRs.getInt("status"));
            order.setPayment_time(orderRs.getTimestamp("payment_time"));
            order.setCustomer_name(orderRs.getString("customer_name"));

            orderRs.close();
            orderStmt.close();

            // 获取订单项
            String itemsSql = "SELECT oi.*, t.tea_name, t.image FROM order_item oi " +
                    "JOIN tea t ON oi.tea_id = t.tea_id " +
                    "WHERE oi.order_id = ?";

            PreparedStatement itemsStmt = conn.prepareStatement(itemsSql);
            itemsStmt.setInt(1, orderId);
            ResultSet itemsRs = itemsStmt.executeQuery();

            List<OrderItem> orderItems = new ArrayList<>();

            while (itemsRs.next()) {
                OrderItem item = new OrderItem();
                item.setId(itemsRs.getInt("item_id"));
                item.setOrder_id(itemsRs.getInt("order_id"));
                item.setTea_id(itemsRs.getInt("tea_id"));
                item.setQuantity(itemsRs.getDouble("quantity"));
                item.setUnit_price(itemsRs.getDouble("unit_price"));

                // 设置茶叶信息（用于显示）
                Tea tea = new Tea();
                tea.setTea_name(itemsRs.getString("tea_name"));
                tea.setImage(itemsRs.getString("image"));
                item.setTea(tea);

                orderItems.add(item);
            }

            itemsRs.close();
            itemsStmt.close();
            conn.close();

            // 构建JSON响应
            response.setContentType("application/json");
            StringBuilder json = new StringBuilder();
            json.append("{\"success\":true,");
            json.append("\"order\":{");
            json.append("\"order_id\":").append(order.getOrder_id()).append(",");
            json.append("\"order_no\":\"").append(order.getOrder_no()).append("\",");
            json.append("\"total_amount\":").append(order.getTotal_amount()).append(",");
            json.append("\"status\":").append(order.getStatus()).append(",");
            json.append("\"payment_time\":\"").append(order.getPayment_time()).append("\"");
            json.append("},");
            json.append("\"orderItems\":[");

            for (int i = 0; i < orderItems.size(); i++) {
                OrderItem item = orderItems.get(i);
                json.append("{");
                json.append("\"tea_name\":\"").append(item.getTea().getTea_name()).append("\",");
                json.append("\"tea_image\":\"").append(item.getTea().getImage()).append("\",");
                json.append("\"quantity\":").append(item.getQuantity()).append(",");
                json.append("\"unit_price\":").append(item.getUnit_price()).append(",");
                json.append("\"subtotal\":").append(item.getSubtotal());
                json.append("}");
                if (i < orderItems.size() - 1) {
                    json.append(",");
                }
            }
            json.append("]}");

            response.getWriter().write(json.toString());

        } catch (NumberFormatException | SQLException e) {
            e.printStackTrace();
            response.setContentType("application/json");
            response.getWriter().write("{\"success\":false,\"message\":\"获取订单详情失败\"}");
        }
    }
}
